#include <boost/thread.hpp>
#include <boost/filesystem.hpp>
#include <boost/shared_ptr.hpp>
#include <boost/scoped_ptr.hpp>
#include <boost/lexical_cast.hpp>
#include "DBHelper.h"
#include "../engine/DebugStream.h"

#ifdef _MSC_VER
#pragma comment(lib,"sqlite3.lib")
#endif

using namespace std;

namespace bf=boost::filesystem; 

namespace Client {
namespace Common {

class CSQLiteDBWrapper {

	char* mErrMsg;
	boost::shared_lock<boost::shared_mutex> mSharedLock;

public :

	sqlite3 *mDB;

	CSQLiteDBWrapper(const char* filename, boost::shared_mutex& mutex) : mDB(NULL), mErrMsg(NULL), mSharedLock(mutex)
	{
		//Engine::DebugStream << "open database file : " << filename << '\n';

		if(sqlite3_open(filename, &mDB) != SQLITE_OK) {
			Engine::DebugStream << "Can't open database: " << sqlite3_errmsg(mDB) << '\n';
			throw std::exception("SQL open error");
		}

		//Engine::DebugStream << " Open database : " << filename << '\n';
	}

	CSQLiteDBWrapper(const char* filename) : mDB(NULL), mErrMsg(NULL), mSharedLock()
	{
		//Engine::DebugStream << "open database file : " << filename << '\n';

		if(sqlite3_open(filename, &mDB) != SQLITE_OK) {
			Engine::DebugStream << "Can't open database: " << sqlite3_errmsg(mDB) << '\n';
			throw std::exception("SQL open error");
		}

		//Engine::DebugStream << " Open database : " << filename << '\n';
	}

	~CSQLiteDBWrapper() {
		if(mDB) sqlite3_close(mDB);
		if(mErrMsg) sqlite3_free(mErrMsg);
	}

	void Exec(const char* sql) {

		if(mErrMsg) sqlite3_free(mErrMsg);

		Engine::DebugStream << "CSQLiteDBWrapper::Exec - " << sql << '\n';
		int rc;
		rc = sqlite3_exec(mDB, sql, 0, 0, &mErrMsg);
		if(rc != SQLITE_OK) {
			Engine::DebugStream << "SQL error: '" << sql << "' - " << mErrMsg << '\n';
			throw std::exception("SQL error");
		}
	}
};

static CDBHelper sDBInstance(1);

CDBHelper& CDBHelper::GetInstance()
{
	return sDBInstance;
}

CDBHelper::CDBHelper(int version) : mVersion(version) {

	bf::path db_file = bf::current_path() / "meta.db";
	mPath = db_file.string();
}

void CDBHelper::Init()
{
	bf::path db_file = mPath;
	if(bf::exists(db_file)) return;

	try {
		CSQLiteDBWrapper db(mPath.c_str());
		db.Exec(
			"CREATE TABLE if not exists version ("
			"ver int NOT NULL"
			");"
			);
		db.Exec((string("INSERT INTO version values(") + boost::lexical_cast<string>(mVersion) + ");").c_str());
		db.Exec(
			"CREATE TABLE if not exists vertag ("
			"seq Integer PRIMARY KEY, "
			"device varchar(20), "
			"name varchar(20) NOT NULL, "
			"head int NOT NULL"
			");"
			);
		db.Exec(
			"CREATE TABLE if not exists device ("
			"seq Integer PRIMARY KEY, "
			"_id varchar(20) NOT NULL, "
			"name varchar(20) NOT NULL, "
			"type int"
			");"
			);
		db.Exec(
			"CREATE TABLE if not exists file ("
			"seq Integer PRIMARY KEY, "
			"_id varchar(40) NOT NULL unique, "
			"name varchar(50) NOT NULL, "
			"type int, "
			"version int, "
			"device varchar(20), "
			"store_path varchar(256) unique, "
			"orig_path varchar(256), "
			"store_tag varchar(20), "
			"size Integer, "
			"parent varchar(40), "
			"status int, "
			"sync_count int, "
			"mod_device varchar(20)"
			");"
			);
	}
	catch(std::exception&) {
	}
}

DBCursorRef CDBHelper::Query(string& table, string& cond, string& order)
{
	string sql_count;
	sql_count.append("select count(*) from ").append(table);
	if(cond.length() > 0) sql_count.append(" where ").append(cond);
	sql_count.append(";");

	string sql_value;
	sql_value.append("select * from ").append(table);
	if(cond.length() > 0) sql_value.append(" where ").append(cond);
	if(order.length() > 0) sql_value.append(" order by ").append(order);
	sql_value.append(";");

	DBCursorRef c;

	try {
		boost::shared_ptr<CSQLiteDBWrapper> db(new CSQLiteDBWrapper(mPath.c_str(), mMutex));
		sqlite3_stmt* stmt;
		const char* tail;
		int count;

		if(sqlite3_prepare_v2(db->mDB, sql_count.c_str(), sql_count.length(), &stmt, &tail) != SQLITE_OK) {
			Engine::DebugStream << "count prepare error: '" << sql_count << "' - " << sqlite3_errmsg(db->mDB) << '\n';
			throw std::exception("SQL error");
		}

		sqlite3_step(stmt);
		count = sqlite3_column_int(stmt, 0);
		sqlite3_finalize(stmt);

		//Engine::DebugStream << "query '" << sql_value << "' -  count : " << count << '\n';

		if(count <= 0) return c;

		if(sqlite3_prepare_v2(db->mDB, sql_value.c_str(), sql_value.length(), &stmt, &tail) != SQLITE_OK) {
			Engine::DebugStream << "value prepare error: '" << sql_value << "' - " << sqlite3_errmsg(db->mDB) << '\n';
			throw std::exception("SQL error");
		}

		c = DBCursorRef(new CDBCursor(db, stmt, count));
	}
	catch(std::exception&) {
		c.reset();
	}

	return c;
}

bool CDBHelper::Insert(CRecord& r)
{
	map<string, string> values_set;
	r.GetValueSet(values_set);

	string fields;
	string values;

	for(map<string, string>::iterator i = values_set.begin(); i != values_set.end(); i++) {
		fields.append(i->first).append(",");
		values.append(i->second).append(",");
	}

	// delete the last useless ','
	fields.erase(fields.length()-1); 
	values.erase(values.length()-1);

	try {
		boost::unique_lock<boost::shared_mutex> lock(mMutex);
		CSQLiteDBWrapper db(mPath.c_str());
		string sql = "insert into ";
		sql.append(r.GetTableName()).append("(").append(fields).append(")");
		sql.append(" values(").append(values).append(");");
		db.Exec(sql.c_str());
	}
	catch(std::exception&) {
		return false;
	}

	return true;
}

bool CDBHelper::Delete(CRecord& r)
{
	try {
		boost::unique_lock<boost::shared_mutex> lock(mMutex);
		CSQLiteDBWrapper db(mPath.c_str());
		string sql = "delete from ";
		sql.append(r.GetTableName());
		sql.append(" where seq=").append(boost::lexical_cast<string>(r.mIndex)).append(";");
		db.Exec(sql.c_str());
	}
	catch(std::exception&) {
		return false;
	}

	return true;
}

bool CDBHelper::Update(CRecord& r)
{
	map<string, string> values_set;
	r.GetValueSet(values_set);

	string values;

	for(map<string, string>::iterator i = values_set.begin(); i != values_set.end(); i++) {
		values.append(i->first).append("=").append(i->second).append(",");
	}

	// delete the last useless ','
	values.erase(values.length()-1);

	try {
		boost::unique_lock<boost::shared_mutex> lock(mMutex);
		CSQLiteDBWrapper db(mPath.c_str());
		string sql = "update ";
		sql.append(r.GetTableName())
			.append(" set ").append(values)
			.append(" where seq=").append(boost::lexical_cast<string>(r.mIndex))
			.append(";");
		db.Exec(sql.c_str());
	}
	catch(std::exception&) {
		return false;
	}

	return true;
}

} // Common
} //Client